--------------------------------------------------------
--  File created - Friday-April-19-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table LOG
--------------------------------------------------------

  CREATE TABLE "SIXLABS"."LOG" 
   (	"MSG" VARCHAR2(200 BYTE), 
	"TIMES" TIMESTAMP (6), 
	"TYPE" VARCHAR2(20 BYTE), 
	"PREF" VARCHAR2(20 BYTE), 
	"AMOUNT" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table TABLE1
--------------------------------------------------------

  CREATE TABLE "SIXLABS"."TABLE1" 
   (	"ID" NUMBER, 
	"TYPE" VARCHAR2(20 BYTE), 
	"PREF" VARCHAR2(20 BYTE), 
	"NAME" VARCHAR2(20 BYTE), 
	"AMOUNT" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table TABLE2
--------------------------------------------------------

  CREATE TABLE "SIXLABS"."TABLE2" 
   (	"ID" NUMBER, 
	"PARENT" NUMBER, 
	"NUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index TABLE1_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "SIXLABS"."TABLE1_PK" ON "SIXLABS"."TABLE1" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index TABLE2_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "SIXLABS"."TABLE2_PK" ON "SIXLABS"."TABLE2" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table LOG
--------------------------------------------------------

  ALTER TABLE "SIXLABS"."LOG" MODIFY ("MSG" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."LOG" MODIFY ("TIMES" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."LOG" MODIFY ("TYPE" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."LOG" MODIFY ("PREF" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."LOG" MODIFY ("AMOUNT" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table TABLE1
--------------------------------------------------------

  ALTER TABLE "SIXLABS"."TABLE1" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE1" MODIFY ("TYPE" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE1" MODIFY ("AMOUNT" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE1" MODIFY ("PREF" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE1" MODIFY ("NAME" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE1" ADD CONSTRAINT "TABLE1_AMOUNT_CHK" CHECK ("AMOUNT">0 AND "AMOUNT"<10000) ENABLE;
 
  ALTER TABLE "SIXLABS"."TABLE1" ADD CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;
 
  ALTER TABLE "SIXLABS"."TABLE1" ADD CONSTRAINT "TABLE1_PREF_CHK" CHECK (length(pref) >= 4 and length(pref) <= 10 and regexp_like(pref, '^[A-Za-z]+$')) ENABLE;
 
  ALTER TABLE "SIXLABS"."TABLE1" ADD CONSTRAINT "TABLE1_TYPE_CHK" CHECK (TYPE IN ('BSC', 'BTS', 'TRAU')) DISABLE;
--------------------------------------------------------
--  Constraints for Table TABLE2
--------------------------------------------------------

  ALTER TABLE "SIXLABS"."TABLE2" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE2" MODIFY ("PARENT" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE2" MODIFY ("NUM" NOT NULL ENABLE);
 
  ALTER TABLE "SIXLABS"."TABLE2" ADD CONSTRAINT "TABLE2_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table TABLE2
--------------------------------------------------------

  ALTER TABLE "SIXLABS"."TABLE2" ADD CONSTRAINT "TABLE2_PARENT_FK_TABLE1_ID" FOREIGN KEY ("PARENT")
	  REFERENCES "SIXLABS"."TABLE1" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  DDL for Trigger LOG_FORBID_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."LOG_FORBID_UPDATE" 
BEFORE UPDATE ON LOG 
BEGIN
  raise_application_error( -20016, 'SIXLABS ERROR: LOG: update forbidden' );
END;
/
ALTER TRIGGER "SIXLABS"."LOG_FORBID_UPDATE" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_PROCESS_AFTER_INSERT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_PROCESS_AFTER_INSERT" 
AFTER INSERT ON TABLE1 
FOR EACH ROW 
BEGIN
  table2_process.gen_records(:new.id, :new.amount);
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_PROCESS_AFTER_INSERT" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_PROCESS_AMOUNT_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_PROCESS_AMOUNT_UPDATE" 
BEFORE UPDATE OF AMOUNT ON TABLE1 
FOR EACH ROW 
BEGIN
  if(:new.amount != :old.amount) then
    table2_process.correct_amount(:old.id, :new.amount);
  end if;

  log_process.log_table1_update(:old.type, :old.pref, :new.amount, 'AMOUNT update proceed');
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_PROCESS_AMOUNT_UPDATE" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_CHK_TYPE_INSERT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_CHK_TYPE_INSERT" 
BEFORE INSERT ON TABLE1 
FOR EACH ROW 
BEGIN
  IF :NEW.TYPE not in ('BSC', 'BTS', 'TRAU') then
    raise_application_error( -20011, 'SIXLABS ERROR: Type value wrong, use BSC, BTS, TRAU only' );
    end if;
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_CHK_TYPE_INSERT" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_PROCESS_INSERT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_PROCESS_INSERT" 
BEFORE INSERT ON TABLE1 
FOR EACH ROW 
BEGIN
  :new.pref := upper(:new.pref);
  :new.name := table1_process.gen_name(:new.type, :new.pref);
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_PROCESS_INSERT" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_FORBID_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_FORBID_UPDATE" 
BEFORE UPDATE OF TYPE,PREF,NAME,ID ON TABLE1 
for each row
BEGIN
  log_process.log_table1_update(:new.type, :new.pref, :old.amount, 'TYPE,PREF,NAME,ID update forbidden');
  raise_application_error( -20014, 'SIXLABS ERROR: TABLE1: TYPE,PREF,NAME,ID update forbidden' );
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_FORBID_UPDATE" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE1_SEQ_INSERT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE1_SEQ_INSERT" BEFORE INSERT ON TABLE1 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF :NEW.ID IS NULL THEN
      SELECT TABLE1_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "SIXLABS"."TABLE1_SEQ_INSERT" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TABLE2_FORBID_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SIXLABS"."TABLE2_FORBID_UPDATE" 
BEFORE UPDATE ON TABLE2 
BEGIN
  raise_application_error( -20015, 'SIXLABS ERROR: TABLE2: update forbidden' );
END;
/
ALTER TRIGGER "SIXLABS"."TABLE2_FORBID_UPDATE" ENABLE;
